/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package conceitos.DAO;

import conceitos.modelo.entidades.Aluno;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;



/**
 *
 * @author jeferson
 */
public class AlunoDAO {
    private Connection conexao;

    public AlunoDAO() {
        conexao = Conexao.getInstance();
    }

    // new id
    private Integer newIdAluno() throws SQLException {
        String sql = "select nextval('aluno_idaluno_seq') as new";
        ResultSet rs = conexao.createStatement().executeQuery(sql);
        if (!rs.next()) {
            rs.close();
            return null;
        }
        return rs.getInt("new");
    }
    
    // insert
  
    public int insert(Aluno aluno){
        String sql = "INSERT INTO aluno (idaluno, nome, sexo) VALUES(?,?,?)";
        try {
            aluno.setIdAluno(newIdAluno());
            PreparedStatement ps = conexao.prepareStatement(sql);
            ps.setInt(1, aluno.getIdAluno());
            ps.setString(2, aluno.getNomeAluno());
            ps.setString(3, aluno.getSexoAluno());
            int retorno = ps.executeUpdate();
            conexao.commit();
            if(retorno > 0){
                return aluno.getIdAluno();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }        
        return -1;
    }

    // update
    public boolean update(Aluno aluno) {
        String sql = "update aluno set nome = ?, sexo = ? where idaluno = "+aluno.getIdAluno();
        try {
            PreparedStatement ps = conexao.prepareStatement(sql);

            ps.setString(1, aluno.getNomeAluno());
            ps.setString(2, aluno.getSexoAluno());
            int retorno = ps.executeUpdate();
            conexao.commit();
            if (retorno > 0) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }

    // delete
    public boolean delete(Integer idAluno) {
        String sql = "delete from aluno where idAluno = ?";
        try {
            PreparedStatement ps = conexao.prepareStatement(sql);
            ps.setObject(1, idAluno);
            int deleteAluno = ps.executeUpdate();
            conexao.commit();
            if (deleteAluno > 0) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }
    
        public Aluno select(String filtro) {
        if (filtro == null || filtro.trim().isEmpty()) {
            filtro = "1 = 1";
        }
        String sql = "select * from aluno where " + filtro;
        try {
            Aluno aluno = null;
            ResultSet rs = conexao.createStatement().executeQuery(sql);
            aluno = new Aluno();
            while (rs.next()) {
                if (rs.isLast()) {
                    aluno.setIdAluno(rs.getInt("idaluno"));
                    aluno.setNomeAluno(rs.getString("nome"));
                    aluno.setSexoAluno(rs.getString("sexo"));
                }
            }
            rs.close();
            return aluno;
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return null;
    }

    // select
    public List<Aluno> selectAll(String filtro) {
        if (filtro == null || filtro.trim().isEmpty()) {
            filtro = "1 = 1";
        }
        String sql = "select * from aluno where " + filtro;
        try {
            ResultSet rs = conexao.createStatement().executeQuery(sql);
            List<Aluno> alunos = new ArrayList<Aluno>();
            while (rs.next()) {
                Aluno aluno = new Aluno();
                aluno.setIdAluno(rs.getInt("idaluno"));
                aluno.setNomeAluno(rs.getString("nome"));
                aluno.setSexoAluno(rs.getString("sexo"));
                alunos.add(aluno);
            }
            rs.close();
            return alunos;
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return null;
    }

    public String getSqlPesquisa() {
        String sql = "select idaluno as \"IDALUNO\",      "
                   + "       nome as \"NOME\",  "
                   + "       sexo as \"SEXO\"       "
                   + "  from aluno             "
                   + " where upper(nome || '%' || serie) like upper(?)"
                   + " order by nome";
        return sql;
    }
}
